﻿using System;
using System.Collections;

using SalePartial.IDAL;
using SalePartial.Model;
using SalePartial.DALFactory;
using System.Data;
using System.Data.SqlClient;
using SalePartial.Utility;
using System.Web;
using System.Web.Security;
using System.Text;

namespace SalePartial.BLL
{
    public class UserBLL
    {
        public static DataSet GetAllUserRolesFromDB()
        {
            SqlDataAdapter dadGetRoles = new SqlDataAdapter("XunPan_UsersGetAllRoles", GlobalSetting.ConnectionString);
            dadGetRoles.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataSet dstRoles = new DataSet();
            dadGetRoles.Fill(dstRoles);
            return dstRoles;
        }

        

        public static DataSet Select_Role(string mg_id, string RoleName, string Description)
        {
            DataSet ds = new DataSet();

            StringBuilder sb = new StringBuilder();
            sb.Append("  SELECT");
            sb.Append("  RoleId, ");
            sb.Append("  RoleName, ");
            sb.Append("  Description, ");
            sb.Append("  mg_id");
            sb.Append("  FROM");
            sb.Append("  aspnet_Roles");
            sb.Append("  WHERE ");

            sb.Append("  RoleName LIKE @RoleName");
            sb.Append("  AND mg_id LIKE @mg_id");
            sb.Append("  AND Description LIKE @Description");
            sb.Append("  ORDER BY mg_id");

            if (RoleName == null || RoleName.Trim() == ""
                || RoleName == string.Empty)
            {
                RoleName = "%";
            }
            if (mg_id == null || mg_id.Trim() == ""
                || mg_id == string.Empty)
            {
                mg_id = "%";
            }
            if (Description == null || Description.Trim() == ""
                || Description == string.Empty)
            {
                Description = "%";
            }

            using (SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString))
            {

                using (SqlCommand Sql_Command = new SqlCommand())
                {
                    Sql_Conn.Open();


                    Sql_Command.Connection = Sql_Conn;
                    Sql_Command.CommandText = sb.ToString();
                    Sql_Command.Parameters.AddWithValue("@RoleName", RoleName);
                    Sql_Command.Parameters.AddWithValue("@mg_id", mg_id);
                    Sql_Command.Parameters.AddWithValue("@Description", Description);
 
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = Sql_Command;

                    da.Fill(ds);

                }
                Sql_Conn.Dispose();
                Sql_Conn.Close();
                Sql_Conn.Dispose();
            }
            return ds;
        }
        //FIXME:
        public static DataSet Select_Role(string SortColumn, int startRowIndex,
            int maximumRows, string mg_id, string RoleName, string Description)
        {
            //SqlDataAdapter dadGetRoles = new SqlDataAdapter("aspnet_Roles_GetAllRoles", GlobalSetting.ConnectionString);
            //dadGetRoles.SelectCommand.CommandType = CommandType.StoredProcedure;
            //dadGetRoles.SelectCommand.Parameters.Add(new SqlParameter("@ApplicationName", null));
            //DataSet dstRoles = new DataSet();
            //dadGetRoles.Fill(dstRoles);
            //return dstRoles;
            DataSet ds = new DataSet();

            StringBuilder sb = new StringBuilder();
            sb.Append("  SELECT");
            sb.Append("  RoleName, ");
            sb.Append("  Description, ");
            sb.Append("  mg_id");
            sb.Append("  FROM");
            sb.Append("  ( select ROW_NUMBER() OVER(ORDER BY mg_id) AS ROWNUM,");
            sb.Append("  RoleName, ");
            sb.Append("  Description, ");
            sb.Append("  mg_id");
            sb.Append("  FROM");
            sb.Append("  aspnet_Roles");
            //sb.Append("  WHERE ");
            //sb.Append("  RoleName=@SortColumn");
            sb.Append("  ) R    WHERE ");
            sb.Append("  ROWNUM BETWEEN @StartRowIndex AND @EndRowIndex");

            using (SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString))
            {

                using (SqlCommand Sql_Command = new SqlCommand())
                {
                    Sql_Conn.Open();

                   
                    Sql_Command.Connection = Sql_Conn;
                    Sql_Command.CommandText = sb.ToString();
                    //Sql_Command.Parameters.AddWithValue("@RoleName", RoleName);
                    //Sql_Command.Parameters.AddWithValue("@mg_id", mg_id);
                    //Sql_Command.Parameters.AddWithValue("@Description", Description);
                    Sql_Command.Parameters.AddWithValue("@startRowIndex", startRowIndex);
                    Sql_Command.Parameters.AddWithValue("@EndRowIndex", startRowIndex+maximumRows);

                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = Sql_Command;

                    da.Fill(ds); 

                    
                }
                Sql_Conn.Dispose();
                Sql_Conn.Close();
                Sql_Conn.Dispose();
            }
            return ds;
        }

        public static int GetCount_Role(string SortColumn, int startRowIndex,
            int maximumRows, string mg_id, string RoleName, string Description)
        {
           DataSet ds = Select_Role(SortColumn, startRowIndex, 10, mg_id, RoleName, Description);
           if (ds == null || ds.Tables.Count == 0)
           {
               return 0;
           }
           else
           {
               return ds.Tables[0].Rows.Count;
           }
        }

        public static DataSet Select_Func_Item(string SortColumn, int startRowIndex,
          int maximumRows, string fi_no1, string fi_name1, string visible1,
             string fi_no2, string fi_name2, string visible2)
        {
            //SqlDataAdapter dadGetRoles = new SqlDataAdapter("aspnet_Roles_GetAllRoles", GlobalSetting.ConnectionString);
            //dadGetRoles.SelectCommand.CommandType = CommandType.StoredProcedure;
            //dadGetRoles.SelectCommand.Parameters.Add(new SqlParameter("@ApplicationName", null));
            //DataSet dstRoles = new DataSet();
            //dadGetRoles.Fill(dstRoles);
            //return dstRoles;
            DataSet ds = new DataSet();

            StringBuilder sb = new StringBuilder();
            sb.Append("  SELECT");
            sb.Append("  fi_no1, ");
            sb.Append("  fi_name1, ");
            sb.Append("  visible1,");
            sb.Append("  fi_no2, ");
            sb.Append("  fi_name2, ");
            sb.Append("  visible2");
            sb.Append("  FROM");
            sb.Append("  (SELECT");
            sb.Append("  ROW_NUMBER() OVER(ORDER BY I1.fi_no1,I2.fi_no2) AS ROWNUM,");
            sb.Append("  I1.fi_no1, ");
            sb.Append("  I1.fi_name1, ");
            sb.Append("  I1.is_visible AS visible1,");
            sb.Append("  I2.fi_no2,");
            sb.Append("  I2.fi_name2,");
            sb.Append("  I2.is_visible AS visible2");
            sb.Append("  FROM");
            sb.Append("  Func_Item1 I1,");
            sb.Append("  Func_Item2 I2");
            sb.Append("  WHERE ");
            sb.Append("  I1.fi_no1=I2.fi_no1 ");
            sb.Append("  ) F    WHERE ");
            sb.Append("  ROWNUM BETWEEN @StartRowIndex AND @EndRowIndex");

            using (SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString))
            {

                using (SqlCommand Sql_Command = new SqlCommand())
                {
                    Sql_Conn.Open();


                    Sql_Command.Connection = Sql_Conn;
                    Sql_Command.CommandText = sb.ToString();
                    //Sql_Command.Parameters.AddWithValue("@RoleName", RoleName);
                    //Sql_Command.Parameters.AddWithValue("@mg_id", mg_id);
                    //Sql_Command.Parameters.AddWithValue("@Description", Description);
                    Sql_Command.Parameters.AddWithValue("@startRowIndex", startRowIndex);
                    Sql_Command.Parameters.AddWithValue("@EndRowIndex", startRowIndex + maximumRows);

                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = Sql_Command;

                    da.Fill(ds);


                }
                Sql_Conn.Dispose();
                Sql_Conn.Close();
                Sql_Conn.Dispose();
            }
            return ds;
        }

        public static int GetCount_Func_Item(string SortColumn, int startRowIndex,
           int maximumRows, string fi_no1, string fi_name1, string visible1,
              string fi_no2, string fi_name2, string visible2)
        {
            DataSet ds = Select_Func_Item(SortColumn, startRowIndex, 10, fi_no1, fi_name1, 
                visible1, fi_no2, fi_name2, visible2);
            if (ds == null || ds.Tables.Count == 0)
            {
                return 0;
            }
            else
            {
                return ds.Tables[0].Rows.Count;
            }
        }

        public static DataSet Select_Func_Power(string SortColumn, int startRowIndex,
          int maximumRows, string fi_no1, string fi_no2, string mg_id,
             string mg_name, string mg_d, string is_enable)
        {
            DataSet ds = new DataSet();

            StringBuilder sb = new StringBuilder();
            sb.Append("  SELECT");
            sb.Append("  fi_no1, ");
            sb.Append("  fi_no2, ");
            sb.Append("  mg_id,");
            sb.Append("  RoleName, ");
            sb.Append("  Description, ");
            //sb.Append("  mg_d, ");
            sb.Append("  is_enable");
            sb.Append("  FROM");
            sb.Append("  (SELECT");
            sb.Append("  ROW_NUMBER() OVER(ORDER BY R.mg_id) AS ROWNUM,");
            sb.Append("  P.fi_no1, ");
            sb.Append("  P.fi_no2, ");
            sb.Append("  P.is_enable,");
            sb.Append("  R.mg_id,");
            sb.Append("  R.RoleName,");
            sb.Append("  R.Description  AS Description");
            sb.Append("  FROM");
            sb.Append("  Func_Power P,");
            sb.Append("  aspnet_Roles R");
            sb.Append("  WHERE ");
            sb.Append("  P.Role_mg_id=R.mg_id ");
            sb.Append("  AND P.fi_no1=@fi_no1 ");
            sb.Append("  AND P.fi_no2=@fi_no2 ");
            sb.Append("  ) F    WHERE ");
            sb.Append("  ROWNUM BETWEEN @StartRowIndex AND @EndRowIndex");

            using (SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString))
            {

                using (SqlCommand Sql_Command = new SqlCommand())
                {
                    Sql_Conn.Open();


                    Sql_Command.Connection = Sql_Conn;
                    Sql_Command.CommandText = sb.ToString();
                    Sql_Command.Parameters.AddWithValue("@fi_no1", fi_no1);
                    Sql_Command.Parameters.AddWithValue("@fi_no2", fi_no2);
                    //Sql_Command.Parameters.AddWithValue("@Description", Description);
                    Sql_Command.Parameters.AddWithValue("@startRowIndex", startRowIndex);
                    Sql_Command.Parameters.AddWithValue("@EndRowIndex", startRowIndex + maximumRows);

                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = Sql_Command;

                    da.Fill(ds);


                }
                Sql_Conn.Dispose();
                Sql_Conn.Close();
                Sql_Conn.Dispose();
            }
            return ds;
        }


        public static int GetCount_Func_Power(string SortColumn, int startRowIndex,
          int maximumRows, string fi_no1, string fi_no2, string mg_id,
             string mg_name, string mg_d, string is_enable)
        {
            DataSet ds = Select_Func_Power(SortColumn, startRowIndex, 10, fi_no1, fi_no2,
                mg_id, mg_name, mg_d, is_enable);
            if (ds == null || ds.Tables.Count == 0)
            {
                return 0;
            }
            else
            {
                return ds.Tables[0].Rows.Count;
            }
        }
        //GetAllUserRoles 

        //********************************************************************* 
        // 
        // UpdateRole Method 
        // 
        // Modifies a role in the database. 
        // 
        //********************************************************************* 
        //public static void UpdateRole(string originalRoleName, string newRoleName, string newRoleDescription, string newRoleExplanation)
        //{
        //    SqlConnection conPortal = new SqlConnection(NtPartGlobals.ConnectionString);
        //    SqlCommand cmdUpdate = new SqlCommand("XunPan_UsersUpdateRole", conPortal);
        //    cmdUpdate.CommandType = CommandType.StoredProcedure;
        //    cmdUpdate.Parameters.Add(new SqlParameter("@originalRoleName", originalRoleName));
        //    cmdUpdate.Parameters.Add(new SqlParameter("@newRoleName", newRoleName));
        //    cmdUpdate.Parameters.Add(new SqlParameter("@newRoleDescription", newRoleDescription));
        //    cmdUpdate.Parameters.Add(new SqlParameter("@newRoleExplanation", newRoleExplanation));

        //    conPortal.Open();
        //    cmdUpdate.ExecuteNonQuery();
        //    conPortal.Close();
        //}
        //UpdateRole 

        //********************************************************************* 
        // 
        // AddRole Method 
        // 
        // Adds a new role to the database. 
        // 
        //********************************************************************* 
        public static void AddRole(string roleName, string description, string explanation)
        {
            SqlConnection conPortal = new SqlConnection(GlobalSetting.ConnectionString);
            SqlCommand cmdAdd = new SqlCommand("XunPan_UsersAddRole", conPortal);
            cmdAdd.CommandType = CommandType.StoredProcedure;
            //cmdAdd.Parameters.Add(new SqlParameter("@portalID", NtPartGlobals.PortalID));
            cmdAdd.Parameters.Add(new SqlParameter("@roleName", roleName));
            cmdAdd.Parameters.Add(new SqlParameter("@roleDescription", description));
            cmdAdd.Parameters.Add(new SqlParameter("@roleExplanation", explanation));

            conPortal.Open();
            cmdAdd.ExecuteNonQuery();
            conPortal.Close();
        }
        //AddRole


        public static bool UpdateRole(string mg_id, string roleName, string description, string explanation)
        {
            // 创建 SQL 修改数据的语句
            string SqlString = "Update aspnet_Roles Set RoleName = @RoleName, Description = @Description, mg_explanation = @mg_explanation";
            SqlString = SqlString + " Where mg_id = @mg_id";
            try
            {
                using (SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString))
                {

                    using (SqlCommand Sql_Command = new SqlCommand())
                    {

                        Sql_Conn.Open();


                        Sql_Command.Connection = Sql_Conn;
                        Sql_Command.CommandText = SqlString;

                        // 截取字符串到数据库所规范的大小 sfc.Left(string mdata, int leng)
                        Sql_Command.Parameters.AddWithValue("@mg_id", mg_id);
                        Sql_Command.Parameters.AddWithValue("@RoleName", SqlDbType.NVarChar).Value = roleName;
                        Sql_Command.Parameters.AddWithValue("@Description", SqlDbType.NVarChar).Value = description;
                        Sql_Command.Parameters.AddWithValue("@mg_explanation", SqlDbType.NVarChar).Value = explanation;

                        Sql_Command.ExecuteNonQuery();
                        

                    }
                }
            }
            catch(Exception e)
            {
                return false;
            }

            return true;
        }


        public static bool DeleteRole(string RoleId)
        {
            DataTable dt = new DataTable();

            StringBuilder sb = new StringBuilder();
            sb.Append("  SELECT");
            sb.Append("  mg_id");
            sb.Append("  FROM");
            sb.Append("  aspnet_Roles");
            sb.Append("  WHERE ");
            sb.Append("  RoleId = @RoleId");
            sb.Append("  ORDER BY mg_id");

            try
            {
                using (SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString))
                {

                    using (SqlCommand Sql_Command = new SqlCommand())
                    {
                        Sql_Conn.Open();


                        Sql_Command.Connection = Sql_Conn;
                        Sql_Command.CommandText = sb.ToString();
                        Sql_Command.Parameters.AddWithValue("@RoleId", RoleId);

                        SqlDataAdapter da = new SqlDataAdapter();
                        da.SelectCommand = Sql_Command;

                        da.Fill(dt);

                        if (dt == null || dt.Rows.Count == 0)
                        {
                            return false;
                        }

                        string mg_id = dt.Rows[0][0].ToString();

                        sb.Clear();

                        sb.Append("Delete From Func_Power Where Role_mg_id = @mg_id;");
                        sb.Append("Delete From aspnet_UsersInRoles Where RoleId=@RoleId2;");
                        sb.Append("Delete From aspnet_Roles Where mg_id = @mg_id;");

                        Sql_Command.CommandText = sb.ToString();

                        Sql_Command.Parameters.AddWithValue("@mg_id", mg_id);
                        Sql_Command.Parameters.AddWithValue("@RoleId2", new Guid(RoleId));
                        Sql_Command.ExecuteNonQuery();

                    }
                    Sql_Conn.Dispose();
                    Sql_Conn.Close();
                    Sql_Conn.Dispose();
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }


        public static bool UpdateUser(Guid userid, bool isLockedOut, bool isApproved)
        {
            // 创建 SQL 修改数据的语句
            string SqlString = "Update aspnet_Membership Set IsLockedOut = @IsLockedOut, IsApproved = @IsApproved";
            if (isLockedOut)
            { SqlString = SqlString +" ,LastLockoutDate=getdate()"; }
            SqlString = SqlString + " Where UserId = @UserId";
            try
            {
                using (SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString))
                {

                    using (SqlCommand Sql_Command = new SqlCommand())
                    {

                        Sql_Conn.Open();


                        Sql_Command.Connection = Sql_Conn;
                        Sql_Command.CommandText = SqlString;

                        // 截取字符串到数据库所规范的大小 sfc.Left(string mdata, int leng)
                        Sql_Command.Parameters.AddWithValue("@UserId", userid);
                        Sql_Command.Parameters.AddWithValue("@IsLockedOut", SqlDbType.Bit).Value = isLockedOut;
                        Sql_Command.Parameters.AddWithValue("@IsApproved", SqlDbType.Bit).Value = isApproved;

                        Sql_Command.ExecuteNonQuery();


                    }
                }
            }
            catch (Exception e)
            {
                return false;
            }

            return true;
        }


        //********************************************************************* 
        // 
        // AddRole Method 
        // 
        // Adds a new role to the database. 
        // 
        //********************************************************************* 
        public static bool StoreUserInfoToSession(string username)
        {

            MembershipUser member = Membership.GetUser(username);

            if (member != null)
            {
                HttpContext.Current.Session.Add("s_userID_guid", member.ProviderUserKey);


                SqlDataReader reader = new XPCompany().SelectReaderByUserID(new Guid(member.ProviderUserKey.ToString()));
                if (reader.Read())
                {
                    XPCompanyInfo company = new XPCompanyInfo(reader);
                    HttpContext.Current.Session.Add("s_companyID_guid", company.CompanyID);
                }
                else
                {
                    //无企业数据
                    if (!reader.IsClosed)
                    {
                        reader.Close();
                    }
                }
                return true;
            }

            return false;
        }
        //AddRole


        public static void AddUserInRole(string roleName, string description, string explanation)
        {
            SqlConnection conPortal = new SqlConnection(GlobalSetting.ConnectionString);
            SqlCommand cmdAdd = new SqlCommand("XunPan_UsersAddRole", conPortal);
            cmdAdd.CommandType = CommandType.StoredProcedure;
            //cmdAdd.Parameters.Add(new SqlParameter("@portalID", NtPartGlobals.PortalID));
            cmdAdd.Parameters.Add(new SqlParameter("@roleName", roleName));
            cmdAdd.Parameters.Add(new SqlParameter("@roleDescription", description));
            cmdAdd.Parameters.Add(new SqlParameter("@roleExplanation", explanation));

            conPortal.Open();
            cmdAdd.ExecuteNonQuery();
            conPortal.Close();
        }
        //AddRole


        public static bool UpdateUserInRole(string RoleId, string UserId, string NewRoleId, string RoleName, DateTime ModerateDate)
        {
            // 创建 SQL 修改数据的语句
            string SqlString = "Update aspnet_UsersInRoles Set RoleId = @NewRoleId";
            SqlString = SqlString + " Where UserId = @UserId and RoleId = @RoleId;";

            if (RoleName == "采购商")
            {
                SqlString = SqlString + " Update XPCompany set BuyerModerateDate=@ModerateDate, BuyerModerateStatus =2 where UserID = @UserId and IsApproved = 'True';";
            }
            else if (RoleName == "供应商")
            {
                SqlString = SqlString + " Update XPCompany set VendorModerateDate=@ModerateDate, VendorModerateStatus = 2 where UserID = @UserId and IsApproved = 'False';";
            }
            try
            {
                using (SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString))
                {

                    using (SqlCommand Sql_Command = new SqlCommand())
                    {

                        Sql_Conn.Open();


                        Sql_Command.Connection = Sql_Conn;
                        Sql_Command.CommandText = SqlString;

                        // 截取字符串到数据库所规范的大小 sfc.Left(string mdata, int leng)
                        Sql_Command.Parameters.AddWithValue("@RoleId", new Guid(RoleId));
                        Sql_Command.Parameters.AddWithValue("@UserId",new Guid(UserId)) ;
                        Sql_Command.Parameters.AddWithValue("@NewRoleId", new Guid(NewRoleId));
                        Sql_Command.Parameters.AddWithValue("@RoleName", SqlDbType.NVarChar).Value = RoleName;
                        Sql_Command.Parameters.AddWithValue("@ModerateDate", SqlDbType.DateTime).Value = ModerateDate;

                        

                        Sql_Command.ExecuteNonQuery();


                    }
                }
            }
            catch (Exception e)
            {
                return false;
            }

            return true;
        }


        public static bool DeleteUserInRole(string RoleId,string UserId)
        {
            
            StringBuilder sb = new StringBuilder();
            //sb.Append("Delete From aspnet_Roles Where mg_id = @mg_id;");
            sb.Append("Delete From aspnet_UsersInRoles Where UserId = @UserId and RoleId = @RoleId;");
            //XPCompliyの情報を削除する必要がある？？
            try
            {
                using (SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString))
                {

                    using (SqlCommand Sql_Command = new SqlCommand())
                    {
                        Sql_Conn.Open();


                        Sql_Command.Connection = Sql_Conn;
                        
                        SqlDataAdapter da = new SqlDataAdapter();
                        da.SelectCommand = Sql_Command;

                        Sql_Command.CommandText = sb.ToString();
                        Sql_Command.Parameters.AddWithValue("@RoleId", new Guid(RoleId));
                        Sql_Command.Parameters.AddWithValue("@UserId", new Guid(UserId));
                        Sql_Command.ExecuteNonQuery();

                    }
                    Sql_Conn.Dispose();
                    Sql_Conn.Close();
                    Sql_Conn.Dispose();
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }


        public static DataTable SelectUserInRole(string RoleId, string UserId)
        {
            DataTable dt = new DataTable();
            StringBuilder sb = new StringBuilder();
            //sb.Append("Delete From aspnet_Roles Where mg_id = @mg_id;");
            sb.Append("Select count(*) From aspnet_UsersInRoles Where UserId = @UserId and RoleId = @RoleId;");
            //XPCompliyの情報を削除する必要がある？？
            try
            {
                using (SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString))
                {

                    using (SqlCommand Sql_Command = new SqlCommand())
                    {
                        Sql_Conn.Open();


                        Sql_Command.Connection = Sql_Conn;

                        SqlDataAdapter da = new SqlDataAdapter();
                        da.SelectCommand = Sql_Command;

                        Sql_Command.CommandText = sb.ToString();
                        Sql_Command.Parameters.AddWithValue("@RoleId", new Guid(RoleId));
                        Sql_Command.Parameters.AddWithValue("@UserId", new Guid(UserId));
                        da.Fill(dt);

                    }
                    Sql_Conn.Dispose();
                    Sql_Conn.Close();
                    Sql_Conn.Dispose();
                }
            }
            catch (Exception ex)
            {
                return null;
            }
            return dt;
        }
    }
}
